package com.yumeno.epidemic.mapper;

import com.yumeno.epidemic.beans.EpidemicDetailInfo;
import com.yumeno.epidemic.beans.EpidemicInfo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface EpidemicMapper {

    @Insert(value = "INSERT INTO epidemics " +
            "(epidemics.province_id,epidemics.affirmed,epidemics.suspected,epidemics.isolated,epidemics.cured,epidemics.dead,epidemics.data_year,.epidemics.data_month,epidemics.data_day,epidemics.user_id,epidemics.input_date) " +
            "VALUES(#{provinceId},#{affirmed},#{suspected},#{isolated},#{cured},#{dead},#{dataYear},#{dataMonth}," +
            "#{dataDay},#{userId},#{inputDate})")
    int saveInfo(EpidemicInfo epidemicInfo);

    @Select(value = "SELECT " +
            "temp.province_id," +
            "temp.province_name," +
            "e.affirmed," +
            "e.suspected," +
            "e.isolated," +
            "e.dead," +
            "e.cured," +
            "temp.affirmed_total," +
            "temp.suspected_total," +
            "temp.isolated_total," +
            "temp.dead_total," +
            "temp.cured_total " +
            "FROM epidemics e RIGHT JOIN (SELECT e1.province_id,provinces.province_name,SUM(e1.affirmed) affirmed_total,SUM(e1.suspected) suspected_total,SUM(e1.isolated) isolated_total,SUM(e1.dead) dead_total,SUM(e1.cured) cured_total\n" +
            "FROM epidemics e1 RIGHT JOIN provinces ON e1.province_id=provinces.province_id \n" +
            "GROUP BY e1.province_id,provinces.province_name) temp ON e.province_id=temp.province_id\n" +
            "AND e.data_year=#{param1} " +
            "AND e.data_month=#{param2} " +
            "AND e.data_day=#{param3}")
    List<EpidemicDetailInfo> findLatestData(short year,short month,short day);
}
